"""
成绩管理模块API视图
"""
from rest_framework import viewsets, status
from rest_framework.decorators import action
from rest_framework.response import Response
from rest_framework.permissions import IsAuthenticated
from django.db.models import Q, Avg, Count
from django.contrib.auth.models import User
from openpyxl import load_workbook
import re
from .models import GradeDetail
from .serializers import GradeDetailSerializer, GradeListSerializer
from users.models import UserProfile
from main.models import Major, Classes


class GradeDetailViewSet(viewsets.ModelViewSet):
    """
    成绩管理ViewSet
    
    提供成绩的CRUD操作:
        - list: 获取成绩列表
        - retrieve: 获取成绩详情
        - create: 录入成绩（教师及以上）
        - update: 更新成绩
        - destroy: 删除成绩
        - statistics: 成绩统计
    """
    queryset = GradeDetail.objects.all().select_related('student', 'student__profile')
    serializer_class = GradeDetailSerializer
    permission_classes = [IsAuthenticated]
    
    def get_serializer_class(self):
        if self.action == 'list':
            return GradeListSerializer
        return GradeDetailSerializer
    
    def get_queryset(self):
        """根据用户权限返回不同的查询集"""
        user = self.request.user
        queryset = GradeDetail.objects.all().select_related('student', 'student__profile')
        
        # 学生只能看到自己的成绩
        if not user.is_staff:
            queryset = queryset.filter(student=user)
        
        # 学生筛选（根据学号）
        student_id = self.request.query_params.get('student_id', None)
        if student_id:
            # 同时搜索 profile.student_id 和 username
            queryset = queryset.filter(
                Q(student__profile__student_id__icontains=student_id) | 
                Q(student__username__icontains=student_id)
            )
        
        # 学生姓名筛选
        student_name = self.request.query_params.get('student_name', None)
        if student_name:
            queryset = queryset.filter(student__first_name__icontains=student_name)
        
        # 专业筛选
        major = self.request.query_params.get('major', None)
        if major:
            queryset = queryset.filter(student__profile__major__icontains=major)
        
        # 班级筛选
        class_name = self.request.query_params.get('class_name', None)
        if class_name:
            queryset = queryset.filter(student__profile__class_name__icontains=class_name)
        
        # 学年筛选
        time = self.request.query_params.get('time', None)
        if time:
            queryset = queryset.filter(time=time)
        
        # 课程筛选
        course_name = self.request.query_params.get('course_name', None)
        if course_name:
            queryset = queryset.filter(course_name__icontains=course_name)
        
        # 成绩范围筛选
        min_grade = self.request.query_params.get('min_grade', None)
        if min_grade:
            queryset = queryset.filter(grade__gte=min_grade)
        
        max_grade = self.request.query_params.get('max_grade', None)
        if max_grade:
            queryset = queryset.filter(grade__lte=max_grade)
        
        # 排序（默认按学年降序，学号升序）
        ordering = self.request.query_params.get('ordering', '-time,student__username')
        if ordering:
            queryset = queryset.order_by(*ordering.split(','))
        
        return queryset
    
    def create(self, request, *args, **kwargs):
        """录入成绩（仅教师及以上）"""
        if not request.user.is_staff:
            return Response({
                'code': 403,
                'message': '权限不足，仅教师及以上可录入成绩'
            }, status=status.HTTP_403_FORBIDDEN)
        
        serializer = self.get_serializer(data=request.data)
        serializer.is_valid(raise_exception=True)
        self.perform_create(serializer)
        
        return Response({
            'code': 201,
            'message': '录入成功',
            'data': serializer.data
        }, status=status.HTTP_201_CREATED)
    
    @action(detail=False, methods=['get'])
    def statistics(self, request):
        """成绩统计"""
        class_name = request.query_params.get('class_name', None)
        time = request.query_params.get('time', None)
        
        queryset = GradeDetail.objects.all()
        
        if class_name:
            queryset = queryset.filter(student__profile__class_name=class_name)
        if time:
            queryset = queryset.filter(time=time)
        
        # 统计数据
        stats = queryset.aggregate(
            total_count=Count('id'),
            avg_grade=Avg('grade')
        )
        
        return Response({
            'total_students': stats['total_count'],
            'avg_grade': stats['avg_grade'] or 0,
            'class_name': class_name,
            'time': time
        })
    
    @action(detail=False, methods=['get'])
    def student_view(self, request):
        """成绩视图：按学生分组展示成绩"""
        from django.db.models import Avg, Count, Q, Value
        from django.db.models.functions import Coalesce
        
        # 获取筛选条件
        student_id = request.query_params.get('student_id', None)
        student_name = request.query_params.get('student_name', None)
        major = request.query_params.get('major', None)
        class_name = request.query_params.get('class_name', None)
        time = request.query_params.get('time', None)
        page = int(request.query_params.get('page', 1))
        page_size = int(request.query_params.get('page_size', 10))
        
        # 构建查询
        queryset = GradeDetail.objects.all().select_related('student', 'student__profile')
        
        # 学生筛选
        if student_id:
            queryset = queryset.filter(
                Q(student__profile__student_id__icontains=student_id) | 
                Q(student__username__icontains=student_id)
            )
        if student_name:
            queryset = queryset.filter(student__first_name__icontains=student_name)
        if major:
            queryset = queryset.filter(student__profile__major__icontains=major)
        if class_name:
            queryset = queryset.filter(student__profile__class_name__icontains=class_name)
        if time:
            queryset = queryset.filter(time=time)
        
        # 按学生和学年分组
        from collections import defaultdict
        students_data = defaultdict(list)
        
        for grade in queryset:
            key = (grade.student.id, grade.time or 0)
            students_data[key].append(grade)
        
        # 计算每个学生的统计信息
        result = []
        for (student_id, time), grades in students_data.items():
            if not grades:
                continue
                
            first_grade = grades[0]
            student = first_grade.student
            
            # 获取学号
            if hasattr(student, 'profile') and student.profile and student.profile.student_id:
                stu_id = student.profile.student_id
            else:
                stu_id = student.username
            
            # 获取姓名
            stu_name = student.first_name or student.username
            
            # 获取专业和班级
            if hasattr(student, 'profile') and student.profile:
                major_name = student.profile.major or '-'
                class_name = student.profile.class_name or '-'
            else:
                major_name = '-'
                class_name = '-'
            
            # 计算统计数据
            grade_numbers = []
            grade_list = []
            for g in grades:
                try:
                    grade_num = float(g.grade)
                    grade_numbers.append(grade_num)
                except:
                    pass
                
                grade_list.append({
                    'id': g.id,
                    'course_name': g.course_name,
                    'grade': g.grade
                })
            
            if grade_numbers:
                avg_grade = sum(grade_numbers) / len(grade_numbers)
                pass_count = len([g for g in grade_numbers if g >= 60])
                pass_rate = (pass_count / len(grade_numbers)) * 100
            else:
                avg_grade = 0
                pass_rate = 0
            
            result.append({
                'student_id': stu_id,
                'student_name': stu_name,
                'major': major_name,
                'class_name': class_name,
                'time': time,
                'total_courses': len(grades),
                'avg_grade': round(avg_grade, 2),
                'pass_rate': round(pass_rate, 1),
                'grades': grade_list
            })
        
        # 排序
        result.sort(key=lambda x: (x['time'], x['student_id']))
        
        # 分页
        total = len(result)
        start = (page - 1) * page_size
        end = start + page_size
        page_result = result[start:end]
        
        return Response({
            'code': 200,
            'message': '成功',
            'data': {
                'count': total,
                'results': page_result,
                'page': page,
                'page_size': page_size
            }
        })
    
    @action(detail=False, methods=['get'])
    def student_matrix(self, request):
        """学生成绩矩阵：横向展示所有学生和课程"""
        from collections import defaultdict
        
        # 获取筛选条件
        student_id = request.query_params.get('student_id', None)
        student_name = request.query_params.get('student_name', None)
        major = request.query_params.get('major', None)
        grade_year = request.query_params.get('grade_year', None)
        class_name = request.query_params.get('class_name', None)
        time = request.query_params.get('time', None)
        page = int(request.query_params.get('page', 1))
        page_size = int(request.query_params.get('page_size', 30))
        
        # 构建查询
        queryset = GradeDetail.objects.all().select_related('student', 'student__profile')
        
        # 学生筛选
        if student_id:
            queryset = queryset.filter(
                Q(student__profile__student_id__icontains=student_id) | 
                Q(student__username__icontains=student_id)
            )
        if student_name:
            queryset = queryset.filter(student__first_name__icontains=student_name)
        if major:
            queryset = queryset.filter(student__profile__major__icontains=major)
        if grade_year:
            # 根据年级筛选，如输入23匹配班级名中包含23的
            queryset = queryset.filter(student__profile__class_name__icontains=grade_year[-2:])
        if class_name:
            queryset = queryset.filter(student__profile__class_name__icontains=class_name)
        if time:
            queryset = queryset.filter(time=time)
        
        # 按学生分组
        student_grades_map = defaultdict(lambda: {'grades': {}, 'info': {}})
        all_courses = set()
        
        for grade in queryset:
            student = grade.student
            
            # 获取学号
            if hasattr(student, 'profile') and student.profile and student.profile.student_id:
                stu_id = student.profile.student_id
            else:
                stu_id = student.username
            
            key = f"{stu_id}_{grade.time or 0}"
            
            # 保存学生信息
            if not student_grades_map[key]['info']:
                stu_name = student.first_name or student.username
                if hasattr(student, 'profile') and student.profile:
                    major_name = student.profile.major or '-'
                    cls_name = student.profile.class_name or '-'
                    # 从班级名称中提取年级，如"计231"提取"23"
                    grade_year = '-'
                    if cls_name and len(cls_name) >= 3:
                        import re
                        match = re.search(r'(\d{2})', cls_name)
                        if match:
                            grade_year = '20' + match.group(1)  # 将23转换为2023
                else:
                    major_name = '-'
                    cls_name = '-'
                    grade_year = '-'
                
                student_grades_map[key]['info'] = {
                    'student_id': stu_id,
                    'student_name': stu_name,
                    'major': major_name,
                    'grade_year': grade_year,
                    'class_name': cls_name,
                    'time': grade.time or 0
                }
            
            # 保存课程成绩
            student_grades_map[key]['grades'][grade.course_name] = grade.grade
            all_courses.add(grade.course_name)
        
        # 转换为列表
        students_list = []
        for key, data in student_grades_map.items():
            student_data = data['info'].copy()
            
            # 添加所有课程成绩
            for course in all_courses:
                student_data[f"course_{course}"] = data['grades'].get(course, None)
            
            # 计算统计数据
            grade_numbers = []
            for grade_str in data['grades'].values():
                try:
                    grade_num = float(grade_str)
                    grade_numbers.append(grade_num)
                except:
                    pass
            
            if grade_numbers:
                avg = sum(grade_numbers) / len(grade_numbers)
                pass_count = len([g for g in grade_numbers if g >= 60])
                student_data['avg_grade'] = round(avg, 2)
                student_data['pass_rate'] = round((pass_count / len(grade_numbers)) * 100, 1)
            else:
                student_data['avg_grade'] = 0
                student_data['pass_rate'] = 0
            
            students_list.append(student_data)
        
        # 排序
        students_list.sort(key=lambda x: (x.get('time', 0), x.get('student_id', '')))
        
        # 分页
        total = len(students_list)
        start = (page - 1) * page_size
        end = start + page_size
        page_result = students_list[start:end]
        
        return Response({
            'code': 200,
            'message': '成功',
            'data': {
                'count': total,
                'results': page_result,
                'courses': sorted(list(all_courses)),
                'page': page,
                'page_size': page_size
            }
        })
    
    @action(detail=False, methods=['post'])
    def batch_import(self, request):
        """批量导入成绩（仅教师及以上）"""
        if not request.user.is_staff:
            return Response({
                'code': 403,
                'message': '权限不足，仅教师及以上可批量导入成绩'
            }, status=status.HTTP_403_FORBIDDEN)
        
        file = request.FILES.get('file')
        if not file:
            return Response({
                'code': 400,
                'message': '请上传Excel文件'
            }, status=status.HTTP_400_BAD_REQUEST)
        
        time = request.data.get('time', None)
        if not time:
            return Response({
                'code': 400,
                'message': '请指定学年'
            }, status=status.HTTP_400_BAD_REQUEST)
        
        major_id = request.data.get('major', None)
        if not major_id:
            return Response({
                'code': 400,
                'message': '请选择专业'
            }, status=status.HTTP_400_BAD_REQUEST)
        
        # 获取专业对象
        try:
            major = Major.objects.get(id=major_id)
        except Major.DoesNotExist:
            return Response({
                'code': 400,
                'message': '专业不存在'
            }, status=status.HTTP_400_BAD_REQUEST)
        
        try:
            # 读取Excel文件
            wb = load_workbook(file)
            ws = wb.active
            
            # 读取表头
            headers = [cell.value for cell in ws[1]]
            
            # 查找课程列（包含方括号的列，如"美学[1]"）
            course_columns = []
            for idx, header in enumerate(headers):
                if header and '[' in str(header) and ']' in str(header):
                    # 提取课程名和课程号
                    match = re.match(r'(.+)\[(\d+)\]', str(header))
                    if match:
                        course_name = match.group(1)
                        course_no = int(match.group(2))
                        course_columns.append({
                            'index': idx,
                            'name': course_name,
                            'no': course_no
                        })
            
            # 查找学号列
            student_id_index = headers.index('学号') if '学号' in headers else None
            if student_id_index is None:
                return Response({
                    'code': 400,
                    'message': 'Excel中未找到"学号"列'
                }, status=status.HTTP_400_BAD_REQUEST)
            
            # 统计信息
            success_count = 0
            fail_count = 0
            errors = []
            
            # 逐行处理数据
            for row_idx in range(2, ws.max_row + 1):
                row = [cell.value for cell in ws[row_idx]]
                student_id = str(row[student_id_index]).strip()
                
                # 根据学号查找或创建用户
                user = None
                try:
                    user = User.objects.get(username=student_id)
                    # 用户已存在，更新Profile信息
                    try:
                        # 查找相关列
                        name_index = headers.index('姓名') if '姓名' in headers else None
                        class_index = headers.index('班级') if '班级' in headers else None
                        
                        student_name = str(row[name_index]).strip() if name_index is not None and row[name_index] else None
                        student_class = str(row[class_index]).strip() if class_index is not None and row[class_index] else ''
                        
                        # 如果有班级信息，同步到班级字典
                        if student_class:
                            Classes.objects.get_or_create(
                                class_name=student_class,
                                defaults={
                                    'class_name': student_class,
                                    'major': major
                                }
                            )
                        
                        # 更新用户姓名（如果有）
                        if student_name and not user.first_name:
                            user.first_name = student_name
                            user.save()
                        
                        # 更新或创建Profile
                        if hasattr(user, 'profile') and user.profile:
                            # Profile存在，更新信息
                            profile = user.profile
                            if not profile.major:  # 如果没有专业，更新
                                profile.major = major.major_name
                            if student_class and not profile.class_name:  # 如果没有班级，更新
                                profile.class_name = student_class
                            profile.save()
                        else:
                            # Profile不存在，创建
                            UserProfile.objects.create(
                                user=user,
                                student_id=student_id,
                                class_name=student_class,
                                major=major.major_name
                            )
                    except Exception as e:
                        errors.append(f'第{row_idx}行：更新学号{student_id}的信息失败：{str(e)}')
                        
                except User.DoesNotExist:
                    # 自动创建学生账号，初始密码为学号
                    try:
                        # 查找相关列
                        name_index = headers.index('姓名') if '姓名' in headers else None
                        grade_index = headers.index('年级') if '年级' in headers else None
                        class_index = headers.index('班级') if '班级' in headers else None
                        
                        student_name = str(row[name_index]).strip() if name_index is not None and row[name_index] else student_id
                        student_grade = str(row[grade_index]).strip() if grade_index is not None and row[grade_index] else ''
                        student_class = str(row[class_index]).strip() if class_index is not None and row[class_index] else ''
                        
                        # 如果有班级信息，同步到班级字典
                        if student_class:
                            Classes.objects.get_or_create(
                                class_name=student_class,
                                defaults={
                                    'class_name': student_class,
                                    'major': major
                                }
                            )
                        
                        # 创建用户
                        user = User.objects.create_user(
                            username=student_id,
                            password=student_id,  # 初始密码设为学号
                            first_name=student_name,
                            is_staff=False,  # 普通学生，非管理员
                            is_active=True
                        )
                        
                        # 创建用户Profile
                        UserProfile.objects.create(
                            user=user,
                            student_id=student_id,
                            class_name=student_class,
                            major=major.major_name  # 保存专业名称
                        )
                        
                        errors.append(f'第{row_idx}行：学号{student_id}不存在，已自动创建账号（姓名：{student_name}，班级：{student_class}，初始密码：{student_id}）')
                    except Exception as e:
                        errors.append(f'第{row_idx}行：学号{student_id}创建失败：{str(e)}，已跳过该学生成绩导入')
                        continue  # 创建失败则跳过该学生的所有成绩
                
                # 如果用户不存在且创建失败，user为None，跳过
                if user is None:
                    continue
                
                # 导入每门课程的成绩
                for course in course_columns:
                    grade_value = row[course['index']]
                    
                    # 跳过空成绩
                    if grade_value is None or str(grade_value).strip() == '':
                        continue
                    
                    # 创建或更新成绩记录
                    try:
                        GradeDetail.objects.update_or_create(
                            student=user,
                            course_no=course['no'],
                            time=time,
                            defaults={
                                'course_name': course['name'],
                                'grade': str(grade_value)
                            }
                        )
                        success_count += 1
                    except Exception as e:
                        errors.append(f'第{row_idx}行，课程{course["name"]}：{str(e)}')
                        fail_count += 1
            
            return Response({
                'code': 200,
                'message': '导入完成',
                'data': {
                    'success_count': success_count,
                    'fail_count': fail_count,
                    'errors': errors[:50]  # 最多返回50条错误信息
                }
            })
        
        except Exception as e:
            return Response({
                'code': 500,
                'message': f'导入失败：{str(e)}'
            }, status=status.HTTP_500_INTERNAL_SERVER_ERROR)
